package com.longtop.framework.sys.file.excel;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.write.DateFormat;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.longtop.framework.common.Message;
import com.longtop.framework.security.BaseUncheckedException;
import com.longtop.framework.sys.file.supports.FileControl;
import com.longtop.framework.sys.file.supports.UploadFile;
import com.longtop.framework.util.MapUtils;
import com.longtop.framework.util.StringUtil;
import com.longtop.framework.util.UniqueKeyUtil;
import com.longtop.intelliweb.action.IActionContext;
import com.longtop.intelliweb.view.Forward;

public class ExcelExtractor {

	private String decimalFormat = "0.00";// 保存到小数点后2位
	private String dateFormat = "yyyy-MM-dd";
	private ExcelBean excelBean;
	// 创建工作本
	private WritableWorkbook book = null;
	private Colour bgColor = Colour.ICE_BLUE;
	private Colour groupbgColor = Colour.LIME;
	// 创建表
	// private HSSFSheet demoSheet = null;
	// 表头的单元格个数目
	private short cellNumber;
	private WritableFont wf;
	private WritableCellFormat wcf;
	private WritableSheet ws;
	private int flushSize = 0;
	private int countSize = 0;
	private NumberFormat nf;
	private WritableCellFormat wcfNumber;
	private WritableCellFormat wcfTimestamp;
	private DateFormat dfDate;
	private WritableCellFormat wcfDate;
	private DateFormat dfTS;
	private String[] columnWidth;

	public ExcelExtractor(ExcelBean excelBean) {
		this.excelBean = excelBean;
		init();
	}

	// private volatile static ExcelExtractor excelExcutor = null;
	// public static ExcelExtractor getExcelExtractorIstance() {
	// if (excelExcutor == null) {
	// synchronized (ExcelExtractor.class) {
	// excelExcutor = new ExcelExtractor();
	// }
	// }
	// return excelExcutor;
	// }

	private void init() {
		cellNumber = (short) excelBean.getExcelHeadMap().size();
		columnWidth = new String[cellNumber];
		// 创建数字单元格式对象
		nf = new NumberFormat(decimalFormat);
		wcfNumber = new WritableCellFormat(nf);

		// 创建TimeStamp单元格式对象
		dfTS = new DateFormat(dateFormat + " hh:mm:ss");
		wcfTimestamp = new WritableCellFormat(dfTS);
		// 创建日期单元格式对象
		dfDate = new DateFormat(dateFormat);
		wcfDate = new WritableCellFormat(dfDate);

		String flushsize = Message.getMessage("excel.export.flushsize");
		if (flushsize.equals("")) {
			flushSize = 1000;
		} else {
			flushSize = Integer.parseInt(flushsize);
		}

	}

	public ExcelBean getExcelBean() {
		return excelBean;
	}

	public void setExcelBean(ExcelBean excelBean) {
		this.excelBean = excelBean;
	}

	/**
	 * 创建表头
	 * 
	 * @throws RowsExceededException
	 * @throws WriteException
	 * 
	 * @modify:孙景弘
	 */
	@SuppressWarnings("deprecation")
	private void createTableHeader() throws RowsExceededException, WriteException {
		Map<String, Object> headMap = excelBean.getExcelHeadMap();
		int column = 0;
		int columnG = 0;
		Map<String, Integer> groupColumnMap = excelBean.getGroupColumn();
		if (groupColumnMap != null) {
			column++;
			int ii = 0;
			int jj = 0;
			for (Iterator<Entry<String, Integer>> iterator = groupColumnMap.entrySet().iterator(); iterator.hasNext();) {
				Entry<String, Integer> entry = iterator.next();
				String columnName = entry.getKey();
				int columnSpan = entry.getValue();

				wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
				wcf = new jxl.write.WritableCellFormat(wf);
				wcf.setAlignment(Alignment.CENTRE);
				try {
					wcf.setBackground(groupbgColor);
				} catch (WriteException e) {
					throw new BaseUncheckedException(e.getMessage());
				}
				ws.mergeCells(jj, 0, ii + columnSpan, 0);
				jxl.write.Label label = new jxl.write.Label(jj, columnG, columnName, wcf);
				ws.addCell(label);
				ii = ii + columnSpan;
				jj = ii + 1;
			}
		}
		int i = 0;
		for (Iterator<Entry<String, Object>> iterator = headMap.entrySet().iterator(); iterator.hasNext();) {
			Entry<String, Object> entry = iterator.next();
			String cellName = "";
			Object obj = entry.getValue();
			if (obj instanceof String) {
				cellName = StringUtil.getString(String.valueOf(obj));
			} else {
				if (obj == null) {
					cellName = "未定义列名";
				} else {
					CodeInfoAdapter codeInfoAdapter = (CodeInfoAdapter) obj;
					cellName = StringUtil.getString(codeInfoAdapter.getExportName());
				}
			}
			wf = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false);
			wcf = new jxl.write.WritableCellFormat(wf);
			try {
				wcf.setBackground(bgColor);
			} catch (WriteException e) {
				throw new BaseUncheckedException(e.getMessage());
			}
			jxl.write.Label label = new jxl.write.Label(i, column, cellName, wcf);
			ws.addCell(label);
			i++;

		}
	}

	/**
	 * 创建行
	 * 
	 * @param cells
	 * @param rowIndex
	 * @throws WriteException
	 * @throws RowsExceededException
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	@SuppressWarnings("deprecation")
	private void createTableRow(Map<String, Object> cells, int rowIndex) throws RowsExceededException, WriteException {
		// 创建sheet名
		// demoSheet = demoSheet1;
		// 创建第rowIndex行
		Map<String, Object> headMap = excelBean.getExcelHeadMap();
		Map<String, Integer> groupColumnMap = excelBean.getGroupColumn();
		if (groupColumnMap != null) {
			rowIndex++;
		}
		int i = 0;
		for (Iterator<Entry<String, Object>> iterator = headMap.entrySet().iterator(); iterator.hasNext();) {
			Entry<String, Object> entry = iterator.next();
			// HSSFCell cell = row.createCell((short) i);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
			Object obj = cells.get(entry.getKey());
			Object valueObj = entry.getValue();
			if (valueObj instanceof CodeInfoAdapter) {
				CodeInfoAdapter codeInfoAdapter = (CodeInfoAdapter) valueObj;
				obj = codeInfoAdapter.getDisplay(StringUtil.getString(obj));
			}
			String cellContent = "";
			if (obj != null) {
				if (obj instanceof java.lang.String) {
					cellContent = StringUtil.getString(obj);
					jxl.write.Label label = new jxl.write.Label(i, rowIndex, cellContent);
					int length = cellContent.length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 10);
					} else {
						ws.setColumnView(i, lengthS + 10);
					}

					ws.addCell(label);

				} else if (obj instanceof java.sql.Timestamp) {
					// cellContent = DateUtil.dateToStr(DateUtil.getDate(obj));

					jxl.write.DateTime datetime = new jxl.write.DateTime(i, rowIndex, (Timestamp) obj, wcfTimestamp);
					ws.addCell(datetime);
				} else if (obj instanceof Integer) {
					cellContent = StringUtil.getString(obj);
					jxl.write.Label label = new jxl.write.Label(i, rowIndex, cellContent);
					ws.addCell(label);
				} else if (obj instanceof java.util.Date || obj instanceof java.sql.Date) {
					jxl.write.DateTime datetime = new jxl.write.DateTime(i, rowIndex, (Date) obj, wcfDate);
					int length = StringUtil.getString(obj).length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 5);
					} else {
						ws.setColumnView(i, lengthS + 5);
					}
					ws.addCell(datetime);
				} else if (obj instanceof BigDecimal) {
					BigDecimal bObj = (BigDecimal) obj;
					jxl.write.Number number = new jxl.write.Number(i, rowIndex, bObj.doubleValue(), wcfNumber);
					int length = StringUtil.getString(obj).length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 5);
					} else {
						ws.setColumnView(i, lengthS + 5);
					}
					ws.addCell(number);
				} else if (obj instanceof Double) {
					jxl.write.Number number = new jxl.write.Number(i, rowIndex, Double.parseDouble(StringUtil.getString(obj)), wcfNumber);
					int length = StringUtil.getString(obj).length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 5);
					} else {
						ws.setColumnView(i, lengthS + 5);
					}
					ws.addCell(number);
				} else if (obj instanceof Float) {
					jxl.write.Number number = new jxl.write.Number(i, rowIndex, Float.parseFloat(StringUtil.getString(obj)), wcfNumber);

					int length = StringUtil.getString(obj).length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 5);
					} else {
						ws.setColumnView(i, lengthS + 5);
					}

					ws.addCell(number);
				} else {
					cellContent = StringUtil.getString(obj);
					jxl.write.Label label = new jxl.write.Label(i, rowIndex, cellContent);
					int length = cellContent.length();
					int lengthS = Integer.parseInt(StringUtil.getString(columnWidth[i], "0"));
					if (length > lengthS) {
						columnWidth[i] = StringUtil.getString(length);
						ws.setColumnView(i, length + 10);
					} else {
						ws.setColumnView(i, lengthS + 10);
					}
					ws.addCell(label);
				}
			} else {
				ws.setColumnView(i, 18);
			}

			i++;
		}
	}

	/**
	 * 创建excelSheet
	 * 
	 * @param valueList
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	@SuppressWarnings("unchecked")
	private void createExcelSheetValue(List valueList) throws RowsExceededException, WriteException {
		boolean bools = excelBean.isPagin();
		Map<String, Map<String, String>> paramsMap = excelBean.getParams();
		Map<String, Object> headMap = excelBean.getExcelHeadMap();
		if (bools) {
			int pageSize = excelBean.getPageSize();
			int total = valueList.size();

			int totalPages = 0;
			if (total % pageSize != 0) {
				totalPages = (total / pageSize) + 1;
			} else {
				totalPages = (total / pageSize);
			}
			for (int i = 0; i < totalPages; i++) {
				// 添加一个工作表
				ws = book.createSheet(excelBean.getSheetName() + "第" + (i + 1) + "页", i);
				// demoSheet = demoWorkBook.createSheet(excelBean.getSheetName()
				// +"第"+(i+1)+"页");

				createTableHeader();// 添加表头

				for (int j = 1; j <= pageSize; j++) {
					if (valueList.size() < 1) {
						break;
					}
					Object obj = valueList.get(0);
					Map objMap = null;
					if (obj instanceof Map) {
						objMap = (Map) obj;
					} else {
						objMap = MapUtils.vo2Map(obj, headMap);
					}

					if (paramsMap != null && paramsMap.size() > 0) {
						for (Iterator<Entry<String, Map<String, String>>> iterator = paramsMap.entrySet().iterator(); iterator.hasNext();) {
							Entry<String, Map<String, String>> entry = iterator.next();
							if (entry == null) {
								continue;
							}
							String strKey = entry.getKey();
							Map<String, String> mapValue = entry.getValue();
							if (objMap.containsKey(strKey)) {
								String value = StringUtil.getString(objMap.get(strKey));
								if (mapValue.containsKey(value)) {
									objMap.put(strKey, mapValue.get(value));
								}
							}
						}
					}
					createTableRow(objMap, j);
					valueList.remove(0);
				}
			}

		} else {
			// 添加一个工作表
			ws = book.createSheet(excelBean.getSheetName(), 0);
			// demoSheet = demoWorkBook.createSheet(excelBean.getSheetName()
			// +"第"+(i+1)+"页");
			createTableHeader();
			for (int i = 1; i <= valueList.size(); i++) {
				Object obj = valueList.get(i - 1);
				Map objMap = null;
				if (obj instanceof Map) {
					objMap = (Map) obj;
				} else {
					objMap = MapUtils.vo2Map(obj, headMap);
				}
				if (paramsMap != null && paramsMap.size() > 0) {
					for (Iterator<Entry<String, Map<String, String>>> iterator = paramsMap.entrySet().iterator(); iterator.hasNext();) {
						Entry<String, Map<String, String>> entry = iterator.next();
						if (entry == null) {
							continue;
						}
						String strKey = entry.getKey();
						Map<String, String> mapValue = entry.getValue();
						if (objMap.containsKey(strKey)) {
							String value = StringUtil.getString(objMap.get(strKey));
							if (mapValue.containsKey(value)) {
								objMap.put(strKey, mapValue.get(value));
							}
						}
					}
				}
				createTableRow(objMap, i);
				// if(i==flushSize){
				// try {
				// book.write();
				// } catch (IOException e) {
				// // TODO Auto-generated catch block
				// e.printStackTrace();
				// }
				// }
			}
		}

	}

	/**
	 * 导出表格
	 * 
	 * @param sheet
	 * @param os
	 * @throws IOException
	 * @throws WriteException
	 * @throws BaseUncheckedException
	 * @throws RowsExceededException
	 */
	// private void exportExcel(HSSFSheet sheet,OutputStream os) throws
	// IOException
	// {
	// sheet.setGridsPrinted(true);
	// demoWorkBook.write(os);
	// }
	/**
	 * 创建Excel
	 * 
	 * @return 返回绝对路径
	 */
	private String createExcel(List valueList) {
		FileControl fileTemp = new FileControl(excelBean.getPath(), excelBean.getOutPutName() + ".xls");
		try {
			fileTemp.createDirectory();
			book = Workbook.createWorkbook(fileTemp.getMyFile());
		} catch (IOException e) {
			throw new BaseUncheckedException("创建excel导出文件时发生错误!错误信息【" + e.getMessage() + "】");
		}
		try {
			createExcelSheetValue(valueList);
		} catch (Exception e) {
			throw new BaseUncheckedException("excel导出文件时发生错误!错误信息【" + e.getMessage() + "】");
		} finally {
			try {
				if (book != null) {
					book.write();
					book.close();
				}
			} catch (Exception e) {
				throw new BaseUncheckedException("excel导出文件时发生错误!错误信息【" + e.getMessage() + "】");
			}
		}

		// FileOutputStream fos = null;
		String path = fileTemp.getMyFile().getAbsolutePath();
		// fos = new FileOutputStream(path);
		// exportExcel(demoSheet,fos);

		System.out.println("【本次导出excel文件的路径为:】" + path);

		return path;
	}

	/**
	 * 构建文件信息
	 * 
	 * @param valueList
	 * @return
	 */
	public UploadFile getUpLoadFile(List valueList) {
		String maxsize = Message.getMessage("excel.export.maxsize");
		if (maxsize.equals("")) {
			throw new BaseUncheckedException("请检查【applicationMessage_zh_CN.properties】配置文件,添加导出excel最大限制数【excel.export.maxsize】。");
		} else {
			int size = 0;
			try {
				size = Integer.parseInt(maxsize);

			} catch (Exception e) {
				throw new BaseUncheckedException("请检查【applicationMessage_zh_CN.properties】配置文件,导出excel最大限制数【excel.export.maxsize】必须为数字。");
			}
			if (valueList.size() > size) {
				throw new BaseUncheckedException("导出数据过大,已经大于本系统限制的最大导出数【" + size + "】,请减少导出数据的数量,重新导出。");
			}
		}
		String path = createExcel(valueList);// 创建导出excel

		UploadFile upLoadFile = new UploadFile();
		upLoadFile.setSavePath(path);
		String downloadfileName = StringUtil.getString(excelBean.getDownloadExcelName());
		if (downloadfileName.equals("")) {
			downloadfileName = UniqueKeyUtil.getUuidKey() + ".xls";
		}
		if (!downloadfileName.endsWith(".xls")) {
			downloadfileName += ".xls";
		}
		upLoadFile.setFileName(downloadfileName);
		return upLoadFile;
	}

	/**
	 * 导出Excel：
	 * 
	 * @param valueList
	 *            List成员为bean对象
	 * @param context
	 * @throws BaseUncheckedException
	 * @throws IOException
	 */
	public void exportExcel(List valueList, IActionContext context) throws BaseUncheckedException {
		UploadFile upf = null;
		upf = getUpLoadFile(valueList);

		Forward fw = new Forward();
		fw.setId("fileServlet.export");
		fw.setPath("/servlet/fileServlet?path=" + upf.getSavePath() + "&name=" + upf.getFileName() + "&task=excel");
		fw.setTarget("_self");
		context.setForward(fw);
	}

	/**
	 * 导出Excel模板：
	 * 
	 * @param valueList
	 *            List成员为bean对象
	 * @param context
	 * @throws BaseUncheckedException
	 * @throws IOException
	 */
	public static void exportExcelTemplate(IActionContext context, String springBean) throws BaseUncheckedException {
		Forward fw = new Forward();
		fw.setId("fileServlet.template");
		fw.setPath("/servlet/fileServlet?task=template&springBean=" + springBean);
		fw.setTarget("_self");
		context.setForward(fw);
	}
}
